package com.iris.live.services.schedule;

import com.iris.live.services.common.DateFormatUtils;
import com.iris.live.services.common.KPIUtils;
import com.iris.live.services.data.repositories.impl.BaseRepository;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;

import javax.persistence.Query;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 经销商销售管理报表定时任务
 */
@Service("SaleManagementReportSchedule")
public class SaleManagementReportSchedule extends BaseRepository {
    private static Logger log = LoggerFactory.getLogger(SaleManagementReportSchedule.class);

    public static void main(String[] args){
        SaleManagementReportSchedule schedule = new SaleManagementReportSchedule();

        System.out.println(schedule.getLastDay("2017", "03"));
    }

    @Scheduled(cron = "0 46 00 * * ?")
    @Transactional
    public void getStatisticsFormDataStorage() {
        //每日凌晨2:30执行前一天所在的月份数据
        try {
            log.info("*******************************经销商销售管理报表定时任务START*******************************");
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(new Date());
            calendar.add(Calendar.DAY_OF_MONTH, -1);
            Date date = calendar.getTime();
            String year = new SimpleDateFormat("yyyy").format(date);
            String month = new SimpleDateFormat("MM").format(date);
            this.doStat(year, month);
            log.info("*******************************经销商销售管理报表定时任务END*******************************");
        } catch (Exception e) {
            log.error("经销商销售管理报表定时任务执行失败！", e);
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
        }
    }

    /**
     * 数据生成
     *
     * @param year
     * @param month
     * @throws Exception
     */
    public void doStat(String year, String month) throws Exception {
        /*initReport(year, month);

        //统计
        String  updateSql = null, selectSql = null,
                startDate = this.getFirstDay(year, month),
                endDate = this.getLastDay(year, month);
        Date currentDate = new Date();
        if((year + "-" + month).equals(DateFormatUtils.getFormatDateToString(currentDate, "yyyy-MM"))){
            endDate = DateFormatUtils.getFormatDateToString(currentDate, DateFormatUtils.TIME_FORMAT_STR_YYYY_MM_DD);
        }

        Map<String, Object> params = new HashMap<>();
        List<Map<String, Object>> values=new ArrayList<>();
        int updated = 0;

        KPIUtils kpiUtils = new KPIUtils();// 起点 1


        //实际采购 批发日期为此年月的车辆台数，采购批发商是谁就算谁的   (完成)
        selectSql =  String.format("SELECT a.许可_BMBS,a.品牌 , sum(if(a.实际采购 is  null ,0 ,a.实际采购)) 实际采购   \n" +
                " FROM   (%s)  a\n" +
                " where a.`品牌` is  not  null group  BY a.许可_BMBS,a.品牌    \n" , KPIUtils.purchaseStock);
        selectSql = selectSql.replaceAll("2000-01-01", startDate);
        selectSql = selectSql.replaceAll("2020-01-01", endDate);
        selectSql = selectSql.replaceAll("'MB'", "''");
        values = this.getList(selectSql.toString());
        if(log.isDebugEnabled()) {
            log.debug(selectSql.toString());
        }
        if (values != null) {
            updateSql = " update t_monthly_sale_rpt set actual_wholesale=:actual_wholesale where permit=:permit and model=:model and stat_year=:year and stat_month=:month ";

            for (Map<String, Object> value : values) {
                if (isNull(value.get("许可_BMBS")) || isNull(value.get("品牌") == null)) continue;

                Query query = this.entities.createNativeQuery(updateSql.toString());
                query.setParameter("actual_wholesale", value.get("实际采购"));
                query.setParameter("permit", value.get("许可_BMBS"));
                query.setParameter("model", value.get("品牌"));
                query.setParameter("year", year);
                query.setParameter("month", month);
                updated = query.executeUpdate();
                if (updated <= 0) {
                    log.warn(String.format("Update failed for permit=%s, model=%s, year=%s, month=%s", value.get("许可_BMBS"), value.get("品牌"), year, month));
                }
            }
        }


        // 零售签署目标（台次）    实际零售NBI（台次）  (完成)
        selectSql = String.format("SELECT a.许可_BMBS ,a.品牌 ,  sum(if(a.新车销量 is  null ,0 ,a.新车销量)) 实际零售NBI , " +
                "  sum(if(a.bmbs_销售目标 is  null ,0 ,a.bmbs_销售目标)) 零售签署目标  \n" +
                " FROM  (%s) a\n" +
                " where a.`品牌` is  not  null group by  a.许可_BMBS ,a.品牌    \n" , KPIUtils.bmbs);
        selectSql = selectSql.replaceAll("2000-01-01", startDate);
        selectSql = selectSql.replaceAll("2020-01-01", endDate);
        selectSql = selectSql.replaceAll("'MB'", "''");
        values = this.getList(selectSql.toString());
        if(log.isDebugEnabled()) {
            log.debug(selectSql.toString());
        }
        if (values != null) {
            updateSql = " update t_monthly_sale_rpt set actual_retail_nbi=:actual_retail_nbi ,signed_retail_ta=:signed_retail_ta where permit=:permit and model=:model and stat_year=:year and stat_month=:month ";
            for (Map<String, Object> value : values) {
                if (isNull(value.get("许可_BMBS")) || isNull(value.get("品牌") == null)) continue;

                Query query = this.entities.createNativeQuery(updateSql.toString());
                query.setParameter("actual_retail_nbi", value.get("实际零售NBI"));
                query.setParameter("signed_retail_ta", value.get("零售签署目标"));
                query.setParameter("permit", value.get("许可_BMBS"));
                query.setParameter("model", value.get("品牌"));
                query.setParameter("year", year);
                query.setParameter("month", month);
                updated = query.executeUpdate();
                if (updated <= 0) {
                    log.warn(String.format("Update failed for permit=%s, model=%s, year=%s, month=%s", value.get("许可_BMBS"), value.get("品牌"), year, month));
                }
            }
        }
        // ROS零售目标（台次）    实际零售ROS（台次） (完成)
        selectSql = String.format("SELECT a.许可_BMBS ,a.品牌 ,  sum(if(a.新车销量 is  null ,0 ,a.新车销量)) 实际零售ROS , " +
                "  sum(if(a.lsh_销售目标 is  null ,0 ,a.lsh_销售目标)) ROS零售目标  \n" +
                " FROM  (%s) a\n" +
                " where a.`品牌` is  not  null group by   a.许可_BMBS ,a.品牌    \n" , KPIUtils.lsh);
        selectSql = selectSql.replaceAll("2000-01-01", startDate);
        selectSql = selectSql.replaceAll("2020-01-01", endDate);
        selectSql = selectSql.replaceAll("'MB'", "''");
        values = this.getList(selectSql.toString());
        if(log.isDebugEnabled()) {
            log.debug(selectSql.toString());
        }
        if (values != null) {
            updateSql = " update t_monthly_sale_rpt set actual_retail_ros=:actual_retail_ros ,ros_retail_ta=:ros_retail_ta where permit=:permit and model=:model and stat_year=:year and stat_month=:month ";
            for (Map<String, Object> value : values) {
                if (isNull(value.get("许可_BMBS")) || isNull(value.get("品牌") == null)) continue;

                Query query = this.entities.createNativeQuery(updateSql.toString());
                query.setParameter("actual_retail_ros", value.get("实际零售ROS"));
                query.setParameter("ros_retail_ta", value.get("ROS零售目标"));
                query.setParameter("permit", value.get("许可_BMBS"));
                query.setParameter("model", value.get("品牌"));
                query.setParameter("year", year);
                query.setParameter("month", month);
                updated = query.executeUpdate();
                if (updated <= 0) {
                    log.warn(String.format("Update failed for permit=%s, model=%s, year=%s, month=%s", value.get("许可_BMBS"), value.get("品牌"), year, month));
                }
            }
        }
        //  长库龄  90天以下、90-149天、大于150天 库存台数  (完成)
        selectSql = String.format("SELECT a.许可_BMBS,a.品牌 , sum(if(a.0_90天 is  null ,0 ,a.0_90天)) lt_90," +
                " sum(if(a.90天_150天 is  null ,0 ,a.90天_150天)) 90_149," +
                " sum(if(a.150天 is  null ,0 ,a.150天)) gt_150 " +
                " FROM  (%s) a\n" +
                " where a.`品牌` is  not  null group by  a.许可_BMBS,a.品牌    \n" , KPIUtils.agingStockLt);
        selectSql = selectSql.replaceAll("2000-01-01", startDate);
        selectSql = selectSql.replaceAll("2020-01-01", endDate);
        selectSql = selectSql.replaceAll("'MB'", "''");
        values = this.getList(selectSql.toString());
        if(log.isDebugEnabled()) {
            log.debug(selectSql.toString());
        }
        if (values != null) {
            updateSql = " update t_monthly_sale_rpt set lt_89_days_stock=:lt_89_days_stock ," +
                    " 90_149_days_stock=:90_149_days_stock,get_150_days_stock=:get_150_days_stock " +
                    " where permit=:permit and model=:model and stat_year=:year and stat_month=:month ";
            for (Map<String, Object> value : values) {
                if (isNull(value.get("许可_BMBS")) || isNull(value.get("品牌") == null)) continue;

                Query query = this.entities.createNativeQuery(updateSql.toString());
                query.setParameter("lt_89_days_stock", value.get("lt_90"));
                query.setParameter("90_149_days_stock", value.get("90_149"));
                query.setParameter("get_150_days_stock", value.get("gt_150"));
                query.setParameter("permit", value.get("许可_BMBS"));
                query.setParameter("model", value.get("品牌"));
                query.setParameter("year", year);
                query.setParameter("month", month);
                query.executeUpdate();
            }
        }

        //GP1      (完成)
        selectSql = String.format("SELECT  a.许可_BMBS,a.品牌,\n" +
                " ROUND(SUM(GP1),2) as GP1金额,\n" +
                " ROUND(SUM(新车外部销售收入),2) as 新车外部销售收入 \n" +
                " FROM  (%s) a" +
                " GROUP BY a.许可_BMBS,a.品牌    \n" , KPIUtils.salesGP1);
        selectSql = selectSql.replaceAll("2000-01-01", startDate);
        selectSql = selectSql.replaceAll("2020-01-01", endDate);
        selectSql = selectSql.replaceAll("'MB'", "''");
        values = this.getList(selectSql.toString());
        if(log.isDebugEnabled()) {
            log.debug(selectSql.toString());
        }
        if (values != null) {
            updateSql = " update t_monthly_sale_rpt set NEW_CAR_EXTERNAL_SALES_REVENUE=:NEW_CAR_EXTERNAL_SALES_REVENUE, ACTUAL_GPI=:ACTUAL_GPI where permit=:permit and model=:model and stat_year=:year and stat_month=:month ";

            for (Map<String, Object> value : values) {
                if (isNull(value.get("许可_BMBS")) || isNull(value.get("品牌") == null)) continue;

                Query query = this.entities.createNativeQuery(updateSql.toString());
                query.setParameter("NEW_CAR_EXTERNAL_SALES_REVENUE", value.get("新车外部销售收入"));
                query.setParameter("ACTUAL_GPI", value.get("GP1金额"));
                query.setParameter("permit", value.get("许可_BMBS"));
                query.setParameter("model", value.get("品牌"));
                query.setParameter("year", year);
                query.setParameter("month", month);
                query.executeUpdate();
            }
        }


        //GP1p5金额     (完成)
        selectSql = String.format("SELECT a.许可_BMBS ,a.品牌,\n" +
                " ROUND(IFNULL(SUM(GP1dot5),0) + IFNULL(SUM(品牌调整值)/COUNT(1), 0) ,2) as GP1p5金额\n" +
                " FROM  (%s) a" +
                " GROUP BY a.许可_BMBS,a.品牌 ", KPIUtils.salesGP1Dot5);
        selectSql = selectSql.replaceAll("2000-01-01", startDate);
        selectSql = selectSql.replaceAll("2020-01-01", endDate);
        selectSql = selectSql.replaceAll("'MB'", "''");
        values = this.getList(selectSql.toString());
        if(log.isDebugEnabled()) {
            log.debug(selectSql.toString());
        }
        if (values != null) {
            updateSql = " update t_monthly_sale_rpt set ACTUAL_GPI_5=:ACTUAL_GPI_5 where permit=:permit and model=:model and stat_year=:year and stat_month=:month ";

            for (Map<String, Object> value : values) {
                if (isNull(value.get("许可_BMBS")) || isNull(value.get("品牌") == null)) continue;

                Query query = this.entities.createNativeQuery(updateSql.toString());
                query.setParameter("ACTUAL_GPI_5", value.get("GP1p5金额"));
                query.setParameter("permit", value.get("许可_BMBS"));
                query.setParameter("model", value.get("品牌"));
                query.setParameter("year", year);
                query.setParameter("month", month);
                query.executeUpdate();
            }
        }

        //GP2金额     (完成)
        selectSql = String.format("SELECT  a.许可_BMBS ,a.品牌,\n" +
                " ROUND(IFNULL(SUM(gp2),0) + IFNULL(SUM(品牌调整值)/COUNT(1),0) ,2) as GP2金额\n" +
                " FROM  (%s) a" +
                " GROUP BY a.许可_BMBS,a.品牌 ", KPIUtils.salesGP2);
        selectSql = selectSql.replaceAll("2000-01-01", startDate);
        selectSql = selectSql.replaceAll("2020-01-01", endDate);
        selectSql = selectSql.replaceAll("'MB'", "''");
        values = this.getList(selectSql.toString());
        if(log.isDebugEnabled()) {
            log.debug(selectSql.toString());
        }
        if (values != null) {
            updateSql = " update t_monthly_sale_rpt set ACTUAL_GPII=:ACTUAL_GPII where permit=:permit and model=:model and stat_year=:year and stat_month=:month ";

            for (Map<String, Object> value : values) {
                if (isNull(value.get("许可_BMBS")) || isNull(value.get("品牌") == null)) continue;

                Query query = this.entities.createNativeQuery(updateSql.toString());
                query.setParameter("ACTUAL_GPII", value.get("GP2金额"));
                query.setParameter("permit", value.get("许可_BMBS"));
                query.setParameter("model", value.get("品牌"));
                query.setParameter("year", year);
                query.setParameter("month", month);
                query.executeUpdate();
            }
        }

        //SI      (完成)
        //开票价金额（开票日期为1月的，不含开票日期小于退票日期的车辆，且销售类型不为B,D,I,P的车辆）
        // BMBS随车SI+批售SI+零售SI+季度SI
        selectSql = String.format("SELECT a.许可_BMBS ,a.品牌,\n" +
                " ROUND(IFNULL(SUM(a.SI金额),0) + IFNULL(SUM(品牌调整值)/COUNT(1),0),2) as SI金额\n" +
                " FROM  (%s) a" +
                " GROUP BY a.许可_BMBS,a.品牌 ", kpiUtils.siMoney);
        selectSql = selectSql.replaceAll("2000-01-01", startDate);
        selectSql = selectSql.replaceAll("2020-01-01", endDate);
        selectSql = selectSql.replaceAll("'MB'", "''");
        values = this.getList(selectSql.toString());
        if(log.isDebugEnabled()) {
            log.debug(selectSql.toString());
        }
        if (values != null) {
            updateSql = " update t_monthly_sale_rpt set ACTUAL_SI=:ACTUAL_SI where permit=:permit and model=:model and stat_year=:year and stat_month=:month ";

            for (Map<String, Object> value : values) {
                if (isNull(value.get("许可_BMBS")) || isNull(value.get("品牌") == null)) continue;

                Query query = this.entities.createNativeQuery(updateSql.toString());
                query.setParameter("ACTUAL_SI", value.get("SI金额"));
                query.setParameter("permit", value.get("许可_BMBS"));
                query.setParameter("model", value.get("品牌"));
                query.setParameter("year", year);
                query.setParameter("month", month);
                query.executeUpdate();
            }
        }

        //新保台数， 新保手续费收入   等待 KpiUtil  完成
        selectSql = String.format("SELECT  a.许可_BMBS,a.品牌,\n" +
                " SUM(a.新保台数) as 新保台数,\n" +
                " ROUND(IFNULL(SUM(保险手续费),0) + IFNULL(SUM(品牌保险手续费调整)/COUNT(1),0) ,2)   as 新保手续费收入, " +
                " ROUND(SUM(首年交强险保费),2) as 首年交强险保费, " +
                " ROUND(SUM(首年商业险保费),2) as 首年商业险保费, " +
                " ROUND(SUM(商业险总保费),2) as 商业险总保费, " +
                " ROUND(SUM(交强险总保费),2) as 交强险总保费" +
                " FROM  (%s) a \n" +
                " GROUP BY a.许可_BMBS,a.品牌 ", KPIUtils.newCarInsuranceCommission);
        selectSql = selectSql.replaceAll("2000-01-01", startDate);
        selectSql = selectSql.replaceAll("2020-01-01", endDate);
        selectSql = selectSql.replaceAll("'MB'", "''");
        values = this.getList(selectSql.toString());
        if (values != null) {
            updateSql = " update t_monthly_sale_rpt set NEW_CAR_INSURANCE=:NEW_CAR_INSURANCE, FIRST_YEAR_COMMERCIAL_INSURANCE_PREMIUM=:FIRST_YEAR_COMMERCIAL_INSURANCE_PREMIUM, " +
                        " COMPULSORY_INSURANCE_PREMIUM = :COMPULSORY_INSURANCE_PREMIUM , COMMERCIAL_INSURANCE_PREMIUM = :COMMERCIAL_INSURANCE_PREMIUM, FIRST_YEAR_COMPULSORY_INSURANCE_PREMIUM=:FIRST_YEAR_COMPULSORY_INSURANCE_PREMIUM, NEW_CAR_INSURANCE_COMMISSION=:NEW_CAR_INSURANCE_COMMISSION where permit=:permit and model=:model and stat_year=:year and stat_month=:month ";

            for (Map<String, Object> value : values) {
                if (isNull(value.get("许可_BMBS")) || isNull(value.get("品牌") == null)) continue;
                Query query = this.entities.createNativeQuery(updateSql.toString());
                query.setParameter("NEW_CAR_INSURANCE", value.get("新保台数"));
                query.setParameter("NEW_CAR_INSURANCE_COMMISSION", value.get("新保手续费收入"));
                query.setParameter("FIRST_YEAR_COMPULSORY_INSURANCE_PREMIUM", value.get("首年交强险保费"));
                query.setParameter("FIRST_YEAR_COMMERCIAL_INSURANCE_PREMIUM", value.get("首年商业险保费"));
                query.setParameter("COMPULSORY_INSURANCE_PREMIUM", value.get("交强险总保费"));
                query.setParameter("COMMERCIAL_INSURANCE_PREMIUM", value.get("商业险总保费"));
                query.setParameter("permit", value.get("许可_BMBS"));
                query.setParameter("model", value.get("品牌"));
                query.setParameter("year", year);
                query.setParameter("month", month);
                query.executeUpdate();
            }
        }

        //后市场产品营业额    测试完成
        selectSql = String.format("SELECT  a.许可_BMBS ,a.品牌,\n" +
                " ROUND(SUM(a.后市场产品营业额) ,2) as 后市场产品营业额\n"+
                " FROM  (%s) a" +
                " GROUP BY a.许可_BMBS,a.品牌 ", KPIUtils.afterMarketProductStatistics);
        selectSql = selectSql.replaceAll("2000-01-01", startDate);
        selectSql = selectSql.replaceAll("2020-01-01", endDate);
        selectSql = selectSql.replaceAll("'MB'", "''");
        values = this.getList(selectSql.toString());
        if (values != null) {
            updateSql = " update t_monthly_sale_rpt set AFTER_MARKET_PRODUCT_REVENUE=:AFTER_MARKET_PRODUCT_REVENUE  where permit=:permit and model=:model and stat_year=:year and stat_month=:month ";

            for (Map<String, Object> value : values) {
                if (isNull(value.get("许可_BMBS")) || isNull(value.get("品牌") == null)) continue;

                Query query = this.entities.createNativeQuery(updateSql.toString());
                query.setParameter("AFTER_MARKET_PRODUCT_REVENUE", value.get("后市场产品营业额"));
                query.setParameter("permit", value.get("许可_BMBS"));
                query.setParameter("model", value.get("品牌"));
                query.setParameter("year", year);
                query.setParameter("month", month);
                query.executeUpdate();
            }
        }

        //后市场产品销售毛利  测试完成
        selectSql = String.format("SELECT  a.许可_BMBS,a.品牌,\n" +
                " ROUND(IFNULL(SUM(a.后市场产品销售毛利),0)+ IFNULL(SUM(品牌后市场产品成本调整)/COUNT(10),0),2) as 后市场产品销售毛利\n" +
                " FROM  (%s) a" +
                " GROUP BY a.许可_BMBS,a.品牌 ", KPIUtils.afterMarketProductStatistics);
        selectSql = selectSql.replaceAll("2000-01-01", startDate);
        selectSql = selectSql.replaceAll("2020-01-01", endDate);
        selectSql = selectSql.replaceAll("'MB'", "''");
        values = this.getList(selectSql.toString());
        if (values != null) {
            updateSql = " update t_monthly_sale_rpt set AFTER_MARKET_PRODUCT_GP=:AFTER_MARKET_PRODUCT_GP where permit=:permit and model=:model and stat_year=:year and stat_month=:month ";

            for (Map<String, Object> value : values) {
                if (isNull(value.get("许可_BMBS")) || isNull(value.get("品牌") == null)) continue;

                Query query = this.entities.createNativeQuery(updateSql.toString());
                query.setParameter("AFTER_MARKET_PRODUCT_GP", value.get("后市场产品销售毛利"));
                query.setParameter("permit", value.get("许可_BMBS"));
                query.setParameter("model", value.get("品牌"));
                query.setParameter("year", year);
                query.setParameter("month", month);
                query.executeUpdate();
            }
        }

        //新车金融台数（NEW_CAR_FINANCING）、金融手续费   调试完成
        selectSql = String.format("SELECT  a.许可_BMBS,a.品牌,\n" +
                " SUM(a.新车金融台数) as 新车金融台数 ,\n" +
                " ROUND(SUM(a.贷款金额),2) as 贷款金额, " +
                " ROUND(IFNULL(SUM(a.金融手续费),0) + IFNULL(SUM(品牌调整值)/COUNT(1),0) ,2) as 金融手续费" +
                " FROM  (%s) a" +
                " GROUP BY a.许可_BMBS,a.品牌 ", KPIUtils.financialInfo);
        selectSql = selectSql.replaceAll("2000-01-01", startDate);
        selectSql = selectSql.replaceAll("2020-01-01", endDate);
        selectSql = selectSql.replaceAll("'MB'", "''");
        values = this.getList(selectSql.toString());
        if (values != null) {
            updateSql = " update t_monthly_sale_rpt set  NEW_CAR_FINANCING=:NEW_CAR_FINANCING,FINANCING_AMOUNT = :FINANCING_AMOUNT, FINANCING_COMMISSION=:FINANCING_COMMISSION where permit=:permit and model=:model and stat_year=:year and stat_month=:month ";

            for (Map<String, Object> value : values) {
                if (isNull(value.get("许可_BMBS")) || isNull(value.get("品牌") == null)) continue;

                Query query = this.entities.createNativeQuery(updateSql.toString());
                query.setParameter("NEW_CAR_FINANCING", value.get("新车金融台数"));
                query.setParameter("FINANCING_COMMISSION", value.get("金融手续费"));
                if(value.get("贷款金额") != null){
                    BigDecimal bd = new BigDecimal(value.get("贷款金额").toString());
                    query.setParameter("FINANCING_AMOUNT", Double.valueOf(bd.toPlainString()));
                }else{
                    query.setParameter("FINANCING_AMOUNT", 0);
                }

                //query.setParameter("FINANCING_AMOUNT", value.get("贷款金额"));
                query.setParameter("permit", value.get("许可_BMBS"));
                query.setParameter("model", value.get("品牌"));
                query.setParameter("year", year);
                query.setParameter("month", month);
                query.executeUpdate();
            }
        }

     //新车Agility台数（NEW_CAR_AGILITY）  ok
        selectSql = String.format("SELECT  a.许可_BMBS,a.品牌,\n" +
                " SUM(a.新车Agility台数) as 新车Agility台数\n" +
                " FROM  (%s) a" +
                " GROUP BY a.许可_BMBS ,a.品牌 ", KPIUtils.agilityPenetrationRate);
        selectSql = selectSql.replaceAll("2000-01-01", startDate);
        selectSql = selectSql.replaceAll("2020-01-01", endDate);
        selectSql = selectSql.replaceAll("'MB'", "''");
        values = this.getList(selectSql.toString());
        if (values != null) {
            updateSql = " update t_monthly_sale_rpt set NEW_CAR_AGILITY=:NEW_CAR_AGILITY where permit=:permit and model=:model and stat_year=:year and stat_month=:month ";

            for (Map<String, Object> value : values) {
                if (isNull(value.get("许可_BMBS")) || isNull(value.get("品牌") == null))

                System.out.println(value.get("许可_BMBS").toString() + "-" + value.get("品牌").toString());

                if(value.get("许可_BMBS").equals("ESUL20") && value.get("品牌").toString().equalsIgnoreCase("smart")){
                    System.out.println();
                }
                Query query = this.entities.createNativeQuery(updateSql.toString());
                query.setParameter("NEW_CAR_AGILITY", value.get("新车Agility台数"));
                query.setParameter("permit", value.get("许可_BMBS"));
                query.setParameter("model", value.get("品牌"));
                query.setParameter("year", year);
                query.setParameter("month", month);
                query.executeUpdate();
            }
        }

        //金融服务费收入（FINANCING_SERVICE_FEE_MARGIN）  ok
        selectSql = String.format("SELECT  a.许可_BMBS,a.品牌,\n" +
                " ROUND(SUM(a.金融服务费),2) as 金融服务费收入" +
                " FROM  (%s) a" +
                " GROUP BY a.许可_BMBS,a.品牌 ", KPIUtils.financingServiceFeePerUnit);
        selectSql = selectSql.replaceAll("2000-01-01", startDate);
        selectSql = selectSql.replaceAll("2020-01-01", endDate);
        selectSql = selectSql.replaceAll("'MB'", "''");
        values = this.getList(selectSql.toString());
        if (values != null) {
            updateSql = " update t_monthly_sale_rpt set FINANCING_SERVICE_FEE_MARGIN=:FINANCING_SERVICE_FEE_MARGIN where permit=:permit and model=:model and stat_year=:year and stat_month=:month ";

            for (Map<String, Object> value : values) {
                if (isNull(value.get("许可_BMBS")) || isNull(value.get("品牌") == null)) continue;

                Query query = this.entities.createNativeQuery(updateSql.toString());
                query.setParameter("FINANCING_SERVICE_FEE_MARGIN", value.get("金融服务费收入"));
                query.setParameter("permit", value.get("许可_BMBS"));
                query.setParameter("model", value.get("品牌"));
                query.setParameter("year", year);
                query.setParameter("month", month);
                query.executeUpdate();
            }
        }

        //上牌服务费，其他服务费
        //开票日期为此月份的上牌服务费，其他服务费,考虑退票  ok
        selectSql = String.format("SELECT  a.许可_BMBS,a.品牌,\n" +
                " ROUND(SUM(a.上牌服务费),2) as 上牌服务费,\n" +
                " ROUND(SUM(a.其他服务费),2) as 其他服务费" +
                " FROM  (%s) a" +
                " GROUP BY a.许可_BMBS,a.品牌 ", KPIUtils.afterMarketServiceRevenue);
        selectSql = selectSql.replaceAll("2000-01-01", startDate);
        selectSql = selectSql.replaceAll("2020-01-01", endDate);
        selectSql = selectSql.replaceAll("'MB'", "''");
        values = this.getList(selectSql.toString());
        if (values != null) {
            updateSql = " update t_monthly_sale_rpt set REGISTRATION_SERVICE_FEE_MARGIN=:REGISTRATION_SERVICE_FEE_MARGIN, OTHER_SERVICE_FEE_MARGIN=:OTHER_SERVICE_FEE_MARGIN where permit=:permit and model=:model and stat_year=:year and stat_month=:month ";

            for (Map<String, Object> value : values) {
                if (isNull(value.get("许可_BMBS")) || isNull(value.get("品牌") == null)) continue;

                Query query = this.entities.createNativeQuery(updateSql.toString());
                query.setParameter("REGISTRATION_SERVICE_FEE_MARGIN", value.get("上牌服务费"));
                query.setParameter("OTHER_SERVICE_FEE_MARGIN", value.get("其他服务费"));
                query.setParameter("permit", value.get("许可_BMBS"));
                query.setParameter("model", value.get("品牌"));
                query.setParameter("year", year);
                query.setParameter("month", month);
                query.executeUpdate();
            }
        }

     *//*   //非实习的销售顾问人数 跨车款会重复统计 ok  旧的只分两个品牌
        selectSql =KPIUtils.scNumber;
        values = this.getList(selectSql.toString());
        if(log.isDebugEnabled()) {
            log.debug(selectSql.toString());
        }
        params.clear();
        values = this.getList(selectSql.toString());
        if (values != null) {
            updateSql = " update t_monthly_sale_rpt set SALES_DEPT_HEADCOUNT=:SALES_DEPT_HEADCOUNT where permit=:permit and model=:model and stat_year=:year and stat_month=:month ";
            String updateSql2 = " update t_monthly_sale_rpt set SALES_DEPT_HEADCOUNT=:SALES_DEPT_HEADCOUNT where permit=:permit and model=:model and stat_year=:year and stat_month=:month ";
            for (Map<String, Object> value : values) {
                if (isNull(value.get("许可_BMBS"))) continue;
                Query query = this.entities.createNativeQuery(updateSql.toString());
                query.setParameter("SALES_DEPT_HEADCOUNT", value.get("smart人数"));
                query.setParameter("permit", value.get("许可_BMBS"));
                query.setParameter("model", "smart");
                query.setParameter("year", year);
                query.setParameter("month", month);
                query.executeUpdate();

                Query query2 = this.entities.createNativeQuery(updateSql2.toString());
                query2.setParameter("SALES_DEPT_HEADCOUNT", value.get("其他顾问人数"));
                query2.setParameter("permit", value.get("许可_BMBS"));
                query2.setParameter("model", "MB");
                query2.setParameter("year", year);
                query2.setParameter("month", month);
                query2.executeUpdate();
            }
        }*//*

        //非实习的销售顾问人数 跨车款会重复统计 ok
        selectSql =KPIUtils.scNumber;
        values = this.getList(selectSql.toString());
        if(log.isDebugEnabled()) {
            log.debug(selectSql.toString());
        }
        params.clear();
        values = this.getList(selectSql.toString());
        if (values != null) {
            updateSql = " update t_monthly_sale_rpt set SALES_DEPT_HEADCOUNT=:SALES_DEPT_HEADCOUNT " +
                    " where permit=:permit and model=:model and stat_year=:year and stat_month=:month  ";
            for (Map<String, Object> value : values) {
                if (isNull(value.get("许可_BMBS"))) continue;
                Query query = this.entities.createNativeQuery(updateSql.toString());
                query.setParameter("SALES_DEPT_HEADCOUNT", value.get("销售顾问人数"));
                query.setParameter("permit", value.get("许可_BMBS"));
                query.setParameter("model", value.get("品牌"));
                query.setParameter("year", year);
                query.setParameter("month", month);
                query.executeUpdate();
            }
        }
        //销售TGP
        selectSql = String.format("SELECT a.许可_BMBS,a.品牌,\n" +
                " ROUND(SUM(TGP),2) as TGP ,ROUND(SUM(销售毛收入),2)  销售毛收入," +
                "ROUND(SUM(TGP)/SUM(销售毛收入),5)  销售T.GP率" +
                " FROM  (%s) a" +
                " GROUP BY a.许可_BMBS,a.品牌 ", KPIUtils.salesTGP);
        selectSql = selectSql.replaceAll("2000-01-01", startDate);
        selectSql = selectSql.replaceAll("2020-01-01", endDate);
        selectSql = selectSql.replaceAll("'MB'", "''");
        values = this.getList(selectSql.toString());
        if (values != null) {
            updateSql = " update t_monthly_sale_rpt set SALES_TRANSACTION_GP=:SALES_TRANSACTION_GP,SALES_GROSS_INCOME = :SALES_GROSS_INCOME " +
                    "set SALES_TRANSACTION_GP_PER=:SALES_TRANSACTION_GP_PER  where permit=:permit and model=:model and stat_year=:year and stat_month=:month ";

            for (Map<String, Object> value : values) {
                if (isNull(value.get("许可_BMBS")) || isNull(value.get("品牌") == null)) continue;
                Query query = this.entities.createNativeQuery(updateSql.toString());
                query.setParameter("SALES_TRANSACTION_GP", value.get("TGP"));
                query.setParameter("SALES_GROSS_INCOME", value.get("销售毛收入"));
                query.setParameter("SALES_TRANSACTION_GP_PER", value.get("销售T.GP率"));
                query.setParameter("permit", value.get("许可_BMBS"));
                query.setParameter("model", value.get("品牌"));
                query.setParameter("year", year);
                query.setParameter("month", month);
                query.executeUpdate();
            }
        }*/
    }

    /**
     * 根据许可和品牌初始化统计数据
     *
     * @param year
     * @param month
     * @throws Exception
     */
    private void initReport(String year, String month) throws Exception {
        StringBuffer sb = new StringBuffer();

        sb.append(" delete from t_monthly_sale_rpt where stat_Year=:statYear and stat_Month = :statMonth ");
        Query deleteQuery = this.entities.createNativeQuery(sb.toString());
        deleteQuery.setParameter("statYear", year);
        deleteQuery.setParameter("statMonth", month);
        deleteQuery.executeUpdate();

        sb.setLength(0);
        sb.append(" insert into t_monthly_sale_rpt(permit, model, stat_Year, stat_Month) ");
        sb.append(" select a.许可_BMBS, b.品牌 , :statYear, :statMonth from lsh经销商表 a, (select distinct 品牌  from lsh车型 where ! (品牌 is null or 品牌 = '')) b where a.是否测试='否'");
        Query query = this.entities.createNativeQuery(sb.toString());
        query.setParameter("statYear", year);
        query.setParameter("statMonth", month);
        query.executeUpdate();
    }

    /**
     * @param year
     * @param month
     * @return
     */
    private String getFirstDay(String year, String month) {
        return year + "-" + month + "-01";
    }

    /**
     * @param year
     * @param month
     * @return
     */
    private String getLastDay(String year, String month) {
        Calendar calendar = Calendar.getInstance();
        calendar.set(Integer.parseInt(year), Integer.parseInt(month) - 1, 1);

        int maxDay = calendar.getActualMaximum(Calendar.DATE);

        return year + '-' + month + '-' + maxDay;
    }

    private boolean isNull(Object value) {
        return value == null || value.toString().trim().equals("");
    }
}

